﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Common.Entities;

namespace Common.Models
{
    public class GoodRepository
    {
        public Good GetGoodById(int goodId)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                string sqlQuery =
                    "SELECT IdGood, Name, Remaining, Price1,Price2, Price3, Price4, SkladId, TrademarkId, Package, GroupId, Article, Price4, Price3, Price2 " +
                    "FROM Goods " +
                    "Where IdGood = @IdGood";

                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@IdGood", goodId);
                    using (SqlCeDataReader dr = command.ExecuteReader())
                    {
                        Good good = new Good();
                        if (dr.Read())
                        {
                            int idGood = dr.GetOrdinal("IdGood");
                            int name = dr.GetOrdinal("Name");
                            int remaining = dr.GetOrdinal("Remaining");
                            int price1 = dr.GetOrdinal("Price1");
                            int price2 = dr.GetOrdinal("Price2");
                            int price3 = dr.GetOrdinal("Price3");
                            int price4 = dr.GetOrdinal("Price4");
                            good.idGood = dr.GetInt32(idGood);
                            good.Name = dr.IsDBNull(name) ? string.Empty : dr.GetString(name);
                            good.Price1 = dr.IsDBNull(price1) ? 0.00d : dr.GetDouble(price1);
                            good.Price2 = dr.IsDBNull(price2) ? 0.00d : dr.GetDouble(price2);
                            good.Price3 = dr.IsDBNull(price3) ? 0.00d : dr.GetDouble(price3);
                            good.Price4 = dr.IsDBNull(price4) ? 0.00d : dr.GetDouble(price4);
                            good.Remaining = dr.IsDBNull(remaining) ? 0.00d : dr.GetDouble(remaining);
                        }
                        return good;
                    }
                }
            }
        }

        public List<Good> GetGoods ()
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                string sqlQuery =
                    "SELECT IdGood, Name, Remaining, Price1,Price2, Price3, Price4, SkladId, TrademarkId, Package, GroupId, Article, Price4, Price3, Price2 " +
                    "FROM Goods ";

                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    using (SqlCeDataReader dr = command.ExecuteReader())
                    {
                        List<Good> goods = new List<Good>();
                        while (dr.Read())
                        {
                            Good good = new Good();
                            int idGood = dr.GetOrdinal("IdGood");
                            int name = dr.GetOrdinal("Name");
                            int remaining = dr.GetOrdinal("Remaining");
                            int price1 = dr.GetOrdinal("Price1");
                            int price2 = dr.GetOrdinal("Price2");
                            int price3 = dr.GetOrdinal("Price3");
                            int price4 = dr.GetOrdinal("Price4");
                            int group = dr.GetOrdinal("GroupId");
                            int tradeMark = dr.GetOrdinal("tradeMarkId");
                            good.idGood = dr.GetInt32(idGood);
                            good.Name = dr.IsDBNull(name) ? string.Empty : dr.GetString(name);
                            good.Price1 = dr.IsDBNull(price1) ? 0.00d : dr.GetDouble(price1);
                            good.Price2 = dr.IsDBNull(price2) ? 0.00d : dr.GetDouble(price2);
                            good.Price3 = dr.IsDBNull(price3) ? 0.00d : dr.GetDouble(price3);
                            good.Price4 = dr.IsDBNull(price4) ? 0.00d : dr.GetDouble(price4);
                            good.Remaining = dr.IsDBNull(remaining) ? 0.00d : dr.GetDouble(remaining);
                            good.Group = DataManager.GetInstance().Groups.GetGroupById(dr.GetInt32(group));
                            good.TradeMark = DataManager.GetInstance().TradeMarks.GetTradeMarkById(dr.GetInt32(tradeMark));
                            goods.Add(good);
                        }
                        return goods;
                    }
                }
            }
            
        }
        
        public List<Good> GetGoodsIncludeAll ()
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                List<Good> goods = new List<Good>(50);
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "SELECT Goods.Name, Goods.Remaining, Goods.Price1, Goods.Price2, Goods.Price3, Goods.Price4, Goods.Article, Goods.Package, Trademarks.NameTrademark, Trademarks.IdTradeMark, Groups.NameGroup, Groups.IdGroup, Goods.IdGood " +
                    "FROM Groups INNER JOIN Goods ON Groups.IdGroup = Goods.GroupId INNER JOIN Trademarks ON Goods.TrademarkId = Trademarks.IdTrademark ";
                if (!Settings.GetInstance().VisibleNullGoods)
                    sqlQuery += " where Goods.Remaining > 0";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    using (SqlCeDataReader dr = command.ExecuteReader())
                    {
                        Good good;

                        int idGood = dr.GetOrdinal("IdGood");
                        int name = dr.GetOrdinal("Name");
                        int remaining = dr.GetOrdinal("Remaining");
                        int price1 = dr.GetOrdinal("Price1");
                        int price2 = dr.GetOrdinal("Price2");
                        int price3 = dr.GetOrdinal("Price3");
                        int price4 = dr.GetOrdinal("Price4");
                        int idGroup = dr.GetOrdinal("IdGroup");
                        int groupName = dr.GetOrdinal("NameGroup");
                        int idTradeMark = dr.GetOrdinal("IdTradeMark");
                        int tradeMarkName = dr.GetOrdinal("NameTrademark");

                        while (dr.Read())
                        {
                            good = new Good();
                            good.idGood = dr.GetInt32(idGood);
                            good.Name = dr.GetString(name);
                            good.Remaining = dr.IsDBNull(remaining) ? 0 : dr.GetDouble(remaining);
                            good.Price1 = dr.IsDBNull(price1) ? 0d : dr.GetDouble(price1);
                            good.Price2 = dr.IsDBNull(price2) ? 0d : dr.GetDouble(price2);
                            good.Price3 = dr.IsDBNull(price3) ? 0d : dr.GetDouble(price3);
                            good.Price4 = dr.IsDBNull(price4) ? 0d : dr.GetDouble(price4);
                            good.Group = new Group {IdGroup = dr.GetInt32(idGroup), Name = dr.GetString(groupName)};
                            good.TradeMark = new TradeMark
                                                      {
                                                          IdTradeMark = dr.GetInt32(idTradeMark),
                                                          Name = dr.GetString(tradeMarkName)
                                                      };
                            goods.Add(good);
                        }
                        return goods;
                    }
                }
            }

        }
    
    }
}
